Release 370: Database structure optimized (convert CHAR to VARCHAR)

Background

Exact Globe 2003 database used data type CHAR instead of VARCHAR to store data with length more than 10 bytes. CHAR always uses fixed length to store data whereas VARCHAR uses dynamic length which depends on the actual size of the data.

Therefore from a database structure standpoint, the database was not optimized. As a result, the following drawbacks were introduced.

• Record was unnecessarily bigger which caused slower queries because less record can be retrieved in one disk I/O.
• Indexes were bigger which again reduced query performance. (MS SQL Server has a limitation that outlines the retrieval data in size of 64 Kb)

By optimizing the database (convert CHAR to VARCHAR), advantages such as greater performance gain, disk space saving, shorter Windows backup & restore time can be acquired.   

What has been changed

From Release 370, the user is given an option to optimize the database, which converts identified columns from data type CHAR to VARCHAR. This functionality is made accessible via menu path System/General/Table list as shown in Image 1.

Image 1

In order to optimize the database, click on the ‘Optimizing database structure’ button located at the bottom button bar (as shown in Image 2). An Optimize Database Structure window will then be displayed as shown in Image 3.

Image 2

Image 3

If ‘Start’ button is clicked, the following message will be displayed (shown in Image 4).

Image 4

After the ‘Yes’ button is clicked, the database structure optimization will begin. In the event that the optimization is successful, the ‘Start’ button in Image 2 will be disabled. The database structure optimization can only be performed once.

Important note:

1. If the process aborted or error occurred during optimization (shown in Image 5), the build-in mechanism will prevent any data loss or database damage. When the database structure optimization process is restarted, conversion will continue at previous aborted/failed point.
   
   Image 5
   
   
2. This optimization has no effect on the alignment of fields (left or right).
   
3. Below is the list of field that will be converted from char to varchar :-

Number	Table name	Field Name	Length	Items
1	Absences	ProjectNumber	20	chartovarchar1
2	Addresses	AddressCode	15	chartovarchar2
3	amutak	bankacc	34
		betaalref	20
		docnumber	30	chartovarchar3
		oms25	60
		project	20
4	amutas	artcode	30
		bankacc	34
		betaalref	20
		docnumber	30	chartovarchar4
		facode	20
		oms25	60
		serialnumber	30
		project	20
5	BacoDiscussions	Class_02_1	30
		Class_02_2	30
		CreatedByFullName	50	chartovarchar5
		ProjectNr	20
		Version	20
		YourRef	30
6	BacoSettings	SettingGroup	40
		SettingName	40	chartovarchar6
		StringValue	255
7	BankTransactions	InstrumentBank	50	chartovarchar7
		OffsetReference	20
8	cicmpy	cmp_fax	25
		cmp_tel	25
		cmp_e_mail	128
		cmp_name	50
		cmp_web	128	chartovarchar8
		PayeeName	40
		PostBankNumber	34
		BankNumber2	34
		PriceList	15
9	cicntp	cnt_dept	40
		cnt_email	128
		cnt_job_desc	50
		cnt_f_fax	25
		cnt_f_mobile	25	chartovarchar9
		cnt_f_tel	25
		cnt_f_name	30
		cnt_l_name	50
10	CompanyLogs	CompanyCode	3	chartovarchar10
11	DocumentWords	Word	20	chartovarchar11
12	frhkrg	del_cnt_job_desc	50
		docnumber	30
		inv_cnt_job_desc	50
		ord_cnt_job_desc	50
		projectnr	20	chartovarchar12
		refer	20
		refer1	20
		refer2	20
		refer3	20
13	frhsrg	artcode	30
		docnumber	30
		oms45	45
		prijslijst	15	chartovarchar13
		projectnr	20
		serial_number	22
14	gbkmut	artcode	30
		bankacc	34
		betaalref	20
		bkstnr	20
		bkstnr_sub	20
		btw_nummer	20	chartovarchar14
		docnumber	30
		facode	20
		oms25	60
		project	20
		vlgn_gbk2	30
15	ItemNumbers	AssetGroup	20
		FiscalGroup	20
		PictureFilename	128
		PriceList	15	chartovarchar15
		PrimaryMethod	20
		Projectnr	20
		SecondaryMethod	20
16	NoiseWords	Word	20	chartovarchar16
17	orhkrg	del_cnt_job_desc	50
		docnumber	30
		inv_cnt_job_desc	50
		ord_cnt_job_desc	50
		projectnr	20	chartovarchar17
		refer	20
		refer1	20
		refer2	20
		refer3	20
18	orhsrg	artcode	30
		oms45	45
		prijslijst	15	chartovarchar18
		projectnr	20
		serie_num	22
19	orkrg	del_cnt_job_desc	50
		docnumber	30
		inv_cnt_job_desc	50
		ord_cnt_job_desc	50
		projectnr	20	chartovarchar19
		refer	20
		refer1	20
		refer2	20
		refer3	20
20	orsrg	artcode	30
		instruction	160
		oms45	45
		oms45_f	45	chartovarchar20
		prijslijst	15
		projectnr	20
		qsrg_line	15
		serie_num	22
21	RequestWords	Word	20	chartovarchar21